<?php
/**
* 
*/
namespace App\Http\Controllers;

use App\Http\Services\DownloadService;
use App\Http\Services\AjaxService;
use App\Http\Services\MCore;
use Illuminate\Http\Request;
use App\Http\Services\DB;

class DatabaseController extends BaseController
{

    /**
     * 获取数据库支持的字符集
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function getAllCharset(Request $request)
    {
        $db = new DB();
        // 获取所有支持的字符集
        $charset_list = $db->show_all_charset();
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS'], $charset_list);
    }

    /**
     * 获取所有引擎
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function getAllEngine()
    {
        $db = new DB();
        // 获取所有支持的字符集
        $charset_list = $db->query('SHOW ENGINES');
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS'], $charset_list);
    }

    /**
     * 获取字段支持的数据类型
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function getAllDataType()
    {
        $db = new DB();
        // 获取所有支持的字符集
        $charset_list = $db->query('SELECT DATA_TYPE FROM information_schema.COLUMNS GROUP BY DATA_TYPE');
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS'], $charset_list);
    }

    /**
     * 新建数据库
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function createDatabase(Request $request)
    {
        $name = MCore::get_not_empty_var('db_name', $request, '数据库名称不能为空');
        $charset_data = MCore::get_not_empty_var('charset', $request, '请选择字符集');
        $charset_arr = explode('&', $charset_data);
        $charset = $charset_arr[0];
        $collate = $charset_arr[1];
        $db = new DB();
        $res = $db->query('CREATE DATABASE ' . $name . ' CHARACTER SET ' . $charset . ' COLLATE ' . $collate);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    /**
     * 删除数据库
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function dropDatabase(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '未选择数据库');
        $db = new DB();
        $db->query('DROP DATABASE IF EXISTS ' . $db_name);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    /**
     * 新建表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function createTable(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '没有选择数据库');
        $table_data = MCore::get_not_empty_var('table_data', $request, '表信息不能为空');
        $data = MCore::get_not_empty_var('data', $request, '数据不能为空');
        $table_data = json_decode($table_data, true);
        $data = json_decode($data, true);
        $flag = 0;
        $string = 'CREATE TABLE ' . $table_data['table_name'] . ' (';
        $string .= 'id int(11) PRIMARY KEY AUTO_INCREMENT,';
        $values = [];
        foreach ($data as $v) {
            $temp = '';
            if ($v['name'] && $v['type']) {
                $temp .= $v['name'] . " " . $v['type'];
            } else {
                $flag = 1;
                break;
            }

            if ($v['field_length']) {
                $temp .= "(" . $v['field_length'] . ")";
            }
            if ($v['not_null']) {
                $temp .= ' NOT NULL';
            }
            if ($v['default']) {
                $temp .= ' DEFAULT ' . $v['default'];
            }
            if ($v['primary_key']) {
                $temp .= ' PRIMARY  KEY';
            }
            if ($v['is_auto_increment']) {
                $temp .= ' AUTO_INCREMENT';
            }
            if ($v['comment']) {
                $temp .= ' COMMENT "' . $v['comment'] . '"';
            }
            $values[] = $temp;
        }
        if ($flag == 1) {
            AjaxService::ajax_return(['error' => '请核对填写信息']);
        }
        $value = implode(',', $values);
        $string .= $value . ')';
        if ($table_data['table_engine']) {
            $string .= ' ENGINE ' . $table_data['table_engine'];
        }
        if ($table_data['table_comment']) {
            $string .= ' COMMENT="' . $table_data['table_comment'] . '"';
        }

        $db = new DB($db_name);
        $db->query($string);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    /**
     * 删除数据表
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function dropTable(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '未选择数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '未选择数据表');
        $db = new DB($db_name);
        $res = $db->execute('DROP TABLE IF EXISTS ' . $table_name);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS'], $res);
    }


    /**
     * 执行原生SQL
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function executeSql(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '未选择数据库');
        $sql = MCore::get_not_empty_var('sql', $request, '请输入SQL语句');
        $db = new DB($db_name);
        $res = $db->query($sql);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS'], $res);
    }

    /**
     * 获取所有的数据库
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function getAllDatabase()
    {
        $db = new DB();
        // 获取所有的数据库
        $db_list = $db->show_all_databases();
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS'], $db_list);
    }

    /**
     * 导入sql文件，需修改
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function importSqlFile(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到选择的数据库');
        $file_path = MCore::get_not_empty_var('file_path', $request, '无法获取到SQL文件');
        $db = new DB($db_name);

        $data = file_get_contents($_SERVER['DOCUMENT_ROOT'].$file_path);
        $sql = explode(';', $data);
        foreach ($sql as $v) {
            $flag = $db->query($v.';');
            if ($flag === false) {
                AjaxService::ajax_return(['error' => $db->get_error()]);
            }
        }
        // 删除文件
        @unlink($_SERVER['DOCUMENT_ROOT'].$file_path);
        AjaxService::ajax_return(['msg' => 'SUCCESS']);

    }

    /**
     * 导出sql文件
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function exportSqlFile(Request $request)
    {
        set_time_limit(0);
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到选择的数据库');
        $is_export_list = MCore::get_var('is_export_list', $request, 0);
        $db_data = session('db');
        $db = new DB($db_name);
        $all_tables = $db->query('SHOW TABLES FROM '.$db_name);

        $mysql_version_data = $db->query('SELECT VERSION()');

        $export_time = date('Y-m-d H:i:s');
        $mysql_version = $mysql_version_data[0][0];
        $php_version = PHP_VERSION;
        $text = <<< EOF
-- MySQL Dump
-- version 1.0.1
-- http://mysql.lushaoming.site
--
-- Host: {$db_data['host']}
-- Export Time: {$export_time}
-- MySQL Version: {$mysql_version}
-- PHP Version: {$php_version}

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: {$db_name}
--

EOF;
        $file_path = $_SERVER['DOCUMENT_ROOT'].'/'.$db_name.' '.date(' Y-m-d').'.sql';
        $tempFile = fopen($_SERVER['DOCUMENT_ROOT'].'/'.$db_name.' '.date(' Y-m-d').'.sql', 'w');
        fwrite($tempFile, $text);

        foreach ($all_tables as $table) {
            $text = <<<EOF
--
-- 表的结构 `{$table[0]}`
--

EOF;

            // 获取创建表的SQL语句
            $data = $db->query('SHOW CREATE TABLE '.$table[0]);
            if (isset($data[0][1])) {
                $text .= <<<EOF
DROP TABLE IF EXISTS `{$table[0]}`;

{$data[0][1]};

EOF;
            }

            fwrite($tempFile, $text);
            // 选择了导出数据才执行
            if ($is_export_list) {
                $text .= <<<EOF
--
-- 表中的数据：`{$table[0]}`
--

EOF;
                // 获取表的所有数据
                $list = $db->query_result_list('SELECT * FROM '.$table[0]);

                if ($list['result']){
                    $insert_sql = "INSERT INTO `{$table[0]}` (";

                    $temp = [];
                    foreach ($list['field'] as $v) {
                        $temp[] = "`{$v}`";
                    }
                    $insert_sql .= implode(',', $temp).') VALUES '.PHP_EOL ;
                    $temp = [];

                    foreach ($list['result'] as $v) {
                        $temp1 = [];
                        for ($i = 0; $i < count($v)/2; $i++){
                            $temp1[] = "'".$v[$i]."'";
                        }
                        $temp[] = "(".implode(',', $temp1).")" ;
                    }
                    $insert_sql .= implode(','.PHP_EOL, $temp).';';
                    fwrite($tempFile, $insert_sql);
                }
            }
        }

        fclose($tempFile);

        // 下载文件
        $download = new DownloadService();
        $download->download($file_path, $db_name.'_'.date('Ymd').'.sql');

        exit();
    }

    /**
     * 删除字段
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param Request $request
     */
    public function dropField(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
        $field_name = MCore::get_not_empty_var('field_name', $request, '无法获取到字段名');
        $db = new DB($db_name);

        $db->drop_table_field($table_name, $field_name);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    public function alterField(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
        $field_name = MCore::get_not_empty_var('field_name', $request, '无法获取到字段名');
        $new_name = MCore::get_not_empty_var('name', $request, '字段名必填');
        $type = MCore::get_not_empty_var('type', $request, '类型必填');
        $length = MCore::get_var('column_length', $request, 0);
        $is_not_null = MCore::get_var('is_not_null', $request, 0);
        $default = MCore::get_var('default', $request, 0);
        $comment = MCore::get_var('comment', $request, 0);
        $primary_key = MCore::get_var('primary_key', $request, 0);
        $auto_increment = MCore::get_var('is_auto_increment', $request, 0);

        $new_data = ' '.$new_name.' '.$type;
        if ($length) {
            $new_data .= '('.$length.')';
        }
        if ($is_not_null == 'on') {
            $new_data .= ' NOT NULL';
        }
//        if ($default !== false) {
//            $new_data .= ' DEFAULT "'.$default.'"';
//        }
        if ($comment !== false) {
            $new_data .= ' COMMENT "'.$comment.'"';
        }
        if ($primary_key == 'on') {
            $new_data .= ' PRIMARY KEY';
        }
        if ($auto_increment == 'on') {
            $new_data .= 'AUTO_INCREMENT';
        }
        $db = new DB($db_name);
        $db->alter_table_field($table_name, $field_name, $new_data);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    // 在$after字段之后增加一个字段
    public function addField(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
        $after = MCore::get_not_empty_var('after', $request, '无法获取到字段名');
        $new_name = MCore::get_not_empty_var('name', $request, '字段名必填');
        $type = MCore::get_not_empty_var('type', $request, '类型必填');
        $length = MCore::get_var('column_length', $request, 0);
        $is_not_null = MCore::get_var('is_not_null', $request, 0);
        $default = MCore::get_var('default', $request, 0);
        $comment = MCore::get_var('comment', $request, 0);
        $primary_key = MCore::get_var('primary_key', $request, 0);
        $auto_increment = MCore::get_var('is_auto_increment', $request, 0);

        $new_data = ' '.$new_name.' '.$type;
        if ($length) {
            $new_data .= '('.$length.')';
        }
        if ($is_not_null == 'on') {
            $new_data .= ' NOT NULL';
        }
        if ($default) {
            $new_data .= ' DEFAULT "'.$default.'"';
        }
        if ($comment !== false) {
            $new_data .= ' COMMENT "'.$comment.'"';
        }
        if ($primary_key == 'on') {
            $new_data .= ' PRIMARY KEY';
        }
        if ($auto_increment == 'on') {
            $new_data .= 'AUTO_INCREMENT';
        }
        $db = new DB($db_name);
        $db->add_table_field($table_name, $after, $new_data);
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    // 更改表名称
    public function alterTableName(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
        $new_name = MCore::get_not_empty_var('new_name', $request, '无法获取到新的表名称');
        $db = new DB($db_name);
        $db->query("ALTER TABLE {$table_name} RENAME {$new_name}");
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    // 清空数据表
    public function emptyTableData(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
        $db = new DB($db_name);
        $db->query("TRUNCATE TABLE {$table_name}");
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    // 删除数据行
//    public function executeSql(Request $request)
//    {
//        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
//        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
//        $db = new DB($db_name);
//        $db->query("TRUNCATE TABLE {$table_name}");
//        if ($db->get_error()) {
//            AjaxService::ajax_return(['error' => $db->get_error()]);
//        }
//        AjaxService::ajax_return(['msg' => 'SUCCESS']);
//    }

    /**
     * 修改表注释
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  POST
     * @url
     * @param Request $request
     */
    public function updateTableComment(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
        $comment = MCore::get_var('comment', $request, 0);

        $db = new DB($db_name);
        $db->query("ALTER TABLE {$table_name} COMMENT '{$comment}'");
        if ($db->get_error()) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        AjaxService::ajax_return(['msg' => 'SUCCESS']);
    }

    /**
     * 新增表数据
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  POST
     * @url     database/insertNewData
     * @param Request $request
     */
    public function insertNewData(Request $request)
    {
        $db_name = MCore::get_not_empty_var('db_name', $request, '无法获取到数据库');
        $table_name = MCore::get_not_empty_var('table_name', $request, '无法获取到数据表');
        $field = MCore::get_not_empty_var('field', $request, '字段为空');
        $data = MCore::get_not_empty_var('data', $request, '数据为空');

        $field = json_decode($field, true);
        $data = json_decode($data, true);
        if (empty($data)) AjaxService::ajax_return(['error' => '数据格式错误']);

        $field_str = implode(',', $field);
        $sql = "INSERT INTO {$table_name} ({$field_str}) VALUES ";

        $values = [];
        foreach ($data as $k => $v) {
            $value = [];
            foreach ($v as $kk => $vv) {
                $value[] = "'" . addslashes($vv) . "'";
            }
            $values[] = "(" . implode(',', $value) . ")";
        }
        $sql .= implode(',', $values);
//        var_dump($sql);exit;
        $db = new DB($db_name);
        $result = $db->execute($sql);
        if ($result === false) {
            AjaxService::ajax_return(['error' => $db->get_error()]);
        }
        $ret = [
            'count' => $result,
        ];
        AjaxService::ajax_return(['msg' => 'SUCCESS'], $ret);
    }
}